﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace AcessaDadosBD.Repositorios
{
    public class GuiaSolicitacaoExtensaoRepository : IRepository<GuiaSolicitacaoExtensao>
    {
        private IDbCommand Command { get; set; }

        public GuiaSolicitacaoExtensaoRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        public GuiaSolicitacaoExtensao GetById(string numeroGuia)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT numeroGuia, local FROM tbGuiaSolicitacaoExtensao WHERE numeroGuia='" + numeroGuia;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    GuiaSolicitacaoExtensao guia = new GuiaSolicitacaoExtensao();
                    guia.NumeroGuia = reader["numeroGuia"].ToString();
                    guia.Local = reader["local"].ToString();
                    guia.ID = int.Parse(reader["idGuiaSolicitacaoExtensao"].ToString());
                    
                    return guia;
                }
                else
                {
                    throw new Exception("Guia não encontrado");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public IQueryable<GuiaSolicitacaoExtensao> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT numeroGuia, local, FROM tbGuiaSolicitacaoExtensao";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<GuiaSolicitacaoExtensao> list = new List<GuiaSolicitacaoExtensao>();
                while (reader.Read())
                {
                    GuiaSolicitacaoExtensao guia = new GuiaSolicitacaoExtensao();
                    guia.NumeroGuia = reader["numeroGuia"].ToString();
                    guia.Local = reader["local"].ToString();
                    guia.ID = int.Parse(reader["idGuiaSolicitacaoExtensao"].ToString());

                    list.Add(guia);
                }
                return list.AsQueryable<GuiaSolicitacaoExtensao>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public int Insert(GuiaSolicitacaoExtensao entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Format("INSERT INTO tbGuiaSolicitacaoExtensao(numeroGuia, local) VALUES ('{0}','{1}')", entity.NumeroGuia, entity.Local);
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();

                return -1;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Delete(GuiaSolicitacaoExtensao entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbGuiaSolicitacaoExtensao WHERE idGuiaSolicitacaoExtensao='" + entity.ID;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Update(GuiaSolicitacaoExtensao entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Format("UPDATE tbGuiaSolicitacaoExtensao SET local='{1}',numeroGuia='{2}' WHERE idGuiaSolicitacaoExtensao='{0}'",
                    entity.ID, entity.Local, entity.NumeroGuia);
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
    }
}
